﻿using Commission.Helpers;
using Commission.Models;
using Commission.Resources;
using Commission.ViewModels;
using ContosoUniversity.Controllers;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Core.Objects;
using System.Data.SqlClient;
using System.Globalization;
using System.Linq;
using System.Web;

namespace Commission.DAL
{
    public class CustomerRepository : ICustomerRepository, IDisposable
    {
        private SchoolContext context;

        public CustomerRepository(SchoolContext context)
        {
            this.context = context;
        }

        public IEnumerable<Customer> GetCustomers()
        {
            return context.Customers
                .Include(c => c.CustomerType)
                .Include(c => c.ParentCustomer)
                .Include(c => c.SponsorCustomer)
                .OrderBy(c => c.UserName)
                ;
                //.ToList();
        }

        public IEnumerable<Customer> GetCustomersForCombo()
        {
            var customers = context.Customers
                .ToList()
                .Select(x => new Customer
                {
                    CustomerID = x.CustomerID,
                    //Name = x.CustomerID.ToString() + " --- " + x.Name,
                    Name = x.UserName + " - " + x.Name,
                });

            return customers;
        }

        public IEnumerable<Customer> GetParentCustomers()
        {
            var customers = context.Customers
                .Where(x => x.SubCustomers.Count < 2)
                ;

            return customers;
        }

        public IEnumerable<Customer> GetCustomersByName(string name)
        {
            var customers = context.Customers
                .Where(x => x.UserName.Equals(name) 
                            || x.Name.Equals(name))
                ;

            return customers;
        }

        public Customer GetCustomerByUserName(string userName)
        {
            //return context.Customers.Find(id).Include(c => c.ParentCustomer);
            return context.Customers
                .Include(c => c.ParentCustomer)
                .Include(c => c.SubCustomers)
                .Include(c => c.CustomerType)
                .Where(s => s.UserName.Equals(userName)).FirstOrDefault<Customer>();
        }

        public Customer GetCustomerLikeUserName(string userName)
        {
            //return context.Customers.Find(id).Include(c => c.ParentCustomer);
            return context.Customers
                .Where(s => s.UserName.Equals(userName)).FirstOrDefault<Customer>();
        }

        public IEnumerable<CustomerType> GetCustomerTypes()
        {
            var customerTypes = context.CustomerTypes
                .OrderBy(c => c.Point)
                .ToList()
                .Select(x => new CustomerType
                {
                    ID = x.ID,
                    //TypeName = x.ID.ToString() + " --- " + x.FullTypeName,
                    TypeName = x.FullTypeName,
                    AttendantFee = x.AttendantFee
                });

            return customerTypes;
        }

        public Customer GetCustomerByID(int? id)
        {
            //return context.Customers.Find(id).Include(c => c.ParentCustomer);
            return context.Customers
                .Include(c => c.ParentCustomer)
                .Include(c => c.SubCustomers)
                .Include(c => c.CustomerType)
                .Where(s => s.CustomerID == id).FirstOrDefault<Customer>();
        }
        
        public void AddCustomer(Customer customer)
        {
            context.Customers.Add(customer);
        }

        public void InsertTransactionHistory(TransactionHistory history)
        {
            context.TransactionHistories.Add(history);
        }

        public bool InsertCustomer(CustomerProduct customerProduct, Customer login, ActionMode mode)
        {
            //using (var dbContextTransaction = context.Database.BeginTransaction())
            //{
                try
                {
                    Customer customer = customerProduct.Customer;
                    customer.Name = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(customer.Name);

                    if (mode == ActionMode.New) { 
                        AddCustomer(customer);
                    }
                    else
                    {
                        CustomerType type = GetCustomerTypeById(customer.CustomerTypeID);
                        customer.UpgradeDate = DateTime.Now;
                        customer.ValidDate = customer.UpgradeDate.AddDays(type.ValidDays);
                        UpdateCustomer(customer);
                    }
                    context.SaveChanges();

                    TransactionHistory history = null;
                    DateTime now = DateTime.Now;

                    SaveDirect(customer, login, now);

                    int transactionId = 0;

                    // Online (tiền thưởng và tiền phí tạo)
                    if (customer.DirectCustomerID > 0)
                    {
                        decimal amount = 0;
                        if (customer.CustomerTypeID == (int)CustomerKind.NP)
                        {
                            amount = 15 * Const.Rate;
                        }
                        else if (customer.CustomerTypeID == (int)CustomerKind.VP)
                        {
                            amount = 20 * Const.Rate;
                        }
                        else if (customer.CustomerTypeID == (int)CustomerKind.AP)
                        {
                            amount = 45 * Const.Rate;
                        }

                        /* vì loại AP ko bị giới hạn tiền nên ko cần kiểm tra
                        decimal remainAmount = GetRemainAmount((int)customer.DirectCustomerID);
                        if (remainAmount < amount)
                        {
                            amount = remainAmount;
                        }
                         */

                        // chỉ đăng ký trong trường hợp có tiền thưởng
                        if (amount > 0) { 
                            history = new TransactionHistory()
                            {
                                CustomerID = (int)customer.DirectCustomerID,
                                SponsoredCustomerID = (int)customer.CustomerID,
                                AccountType = AccountType.Online,
                                AccountDate = now,
                                Amount = amount * new decimal(0.8),
                                ProductAmount = amount * new decimal(0.2),
                                Description = AppResource.MessageOnlineCommission,
                                CreatedUserId = login.CustomerID,
                                CreatedAt = now,
                                UpdatedUserId = login.CustomerID,
                                UpdatedAt = now,
                            };
                            InsertTransactionHistory(history);
                        }

                        CustomerType customerType = context.CustomerTypes
                            .Where(s => s.ID == customer.CustomerTypeID)
                            .FirstOrDefault<CustomerType>();

                        if (mode == ActionMode.New)
                        {
                            customer.Amount80 += 200000;
                        }

                        // chỉ đăng ký trong trường hợp hội viên có đóng tiền
                        if (customer.Amount80 > 0)
                        {
                            amount = -customer.Amount80;

                            history = new TransactionHistory()
                            {
                                CustomerID = (int)customer.DirectCustomerID,
                                SponsoredCustomerID = (int)customer.CustomerID,
                                AccountType = AccountType.OnlinePayment,
                                AccountDate = now,
                                Amount = amount,
                                ProductAmount = decimal.Zero,
                                Description = AppResource.MessageOnlineFee,
                                CreatedUserId = login.CustomerID,
                                CreatedAt = now,
                                UpdatedUserId = login.CustomerID,
                                UpdatedAt = now,
                            };
                            InsertTransactionHistory(history);
                        }

                        context.SaveChanges();
                        transactionId = history.ID;
                    }
                    else
                    {
                        history = new TransactionHistory()
                        {
                            CustomerID = (int)customer.CustomerID,
                            SponsoredCustomerID = (int)login.CustomerID, // người tạo
                            AccountType = AccountType.SignUp,
                            AccountDate = now,
                            //Amount = -customer.ProductAmount,
                            Amount = 0,
                            ProductAmount = decimal.Zero,
                            Description = AppResource.MessageMemberSignup,
                            CreatedUserId = login.CustomerID,
                            CreatedAt = now,
                            UpdatedUserId = login.CustomerID,
                            UpdatedAt = now,
                        };
                        InsertTransactionHistory(history);
                        context.SaveChanges();

                        transactionId = history.ID;
                    }

                    foreach (Product product in customerProduct.Products)
                    {
                        if (product.Quantity < 1 || transactionId == 0) continue;

                        Trading trading = new Trading
                        {
                            CustomerId = customer.CustomerID,
                            ProductId = product.ProductId,
                            Quantity = product.Quantity,
                            Amount = product.Quantity * product.Price,
                            TradeDate = now,
                            DeliveryDate = null,
                            TransactionHistoryId = transactionId,
                            TradeStatus = TradeStatus.Paid,
                            CreatedUserId = login.CustomerID,
                            CreatedAt = now,
                            UpdatedUserId = login.CustomerID,
                            UpdatedAt = now,
                        };
                        context.Tradings.Add(trading);
                    }
                    context.SaveChanges();

                    //dbContextTransaction.Commit();

                    return true;
                }
                catch (Exception ex)
                {
                    Console.Write(ex.Message);
                    //dbContextTransaction.Rollback();
                    return false;
                } 
            //}
        }

        public decimal GetRemainAmount(int customerId)
        {
            Customer customer = GetCustomerByID(customerId);
            decimal totalAmount = this.GetTotalAmountOfDay(customerId, DateTime.Now.Date);

            decimal maxProfitAmount = customer.CustomerType.MaxDailyProfit;
            decimal remain = maxProfitAmount - totalAmount;

            return remain;
        }

        private void SaveDirect(Customer customer, Customer login, DateTime now)
        {
            // Direct
            if (customer.SponsorCustomerID != null)
            {
                CustomerType customerType = this.GetCustomerTypeById(customer.CustomerTypeID);

                // getAmountByCustomer 
                decimal maxSponsorAmount = customerType.MaxSponsorAmount;

                Customer sponsorCustomer = this.GetCustomerByID(customer.SponsorCustomerID);
                SponsorCommission commission = sponsorCustomer.CustomerType.SponsorCommissions
                    .SingleOrDefault(
                        x => x.SponsorCustomerTypeID == sponsorCustomer.CustomerTypeID
                        && x.CustomerTypeID == customer.CustomerTypeID
                    );

                // commission ko the null duoc
                if (commission != null)
                {
                    // tổng tiền đã nhận trong ngày
                    decimal totalAmount = this.GetTotalAmountOfDay(
                        sponsorCustomer.CustomerID, DateTime.Now.Date);

                    decimal maxDailyAmount = sponsorCustomer.CustomerType.MaxDailyProfit;

                    decimal remain = maxDailyAmount - totalAmount;
                    string desc = AppResource.MessageDirectCommission;

                    if (commission.Amount < remain)
                    {
                        remain = commission.Amount;
                    }
                    else
                    {
                        desc = desc + String.Format(AppResource.MessageMaxProfitExceed,
                            commission.Amount, remain);
                    }

                    TransactionHistory history = new TransactionHistory();
                    history.CustomerID = (int)sponsorCustomer.CustomerID;
                    history.SponsoredCustomerID = (int)customer.CustomerID;
                    history.SponsoredCustomer = customer;
                    history.AccountType = AccountType.Direct;
                    history.AccountDate = now;
                    history.Amount = remain * new decimal(0.8);
                    history.ProductAmount = remain * new decimal(0.2);
                    history.Description = desc;
                    history.CreatedUserId = login.CustomerID;
                    history.CreatedAt = now;
                    history.UpdatedUserId = login.CustomerID;
                    history.UpdatedAt = now;
                    InsertTransactionHistory(history);

                    decimal sumAmount = commission.Amount;

                    if (sumAmount < maxSponsorAmount && sponsorCustomer.SponsorCustomer != null)
                    {
                        // Rollup
                        CreateTransaction(
                            customer,
                            (int)sponsorCustomer.SponsorCustomerID,
                            AccountType.RollUp, sumAmount, maxSponsorAmount);
                    }
                }
            }
        }

        /**
         * sponsorCustomerId : hội viên đang rollup
         * maxSponsorAmount : số tiền tối đa có thể nhận khi giới đăng ký hội viên mới
         * sumAmount : số tiền hiện tại đã nhận khi rollup
         */
        private void CreateTransaction(Customer customer, 
            int rollUpCustomerId, AccountType accountType, 
            decimal sumAmount, decimal maxSponsorAmount)
        {
            int customerId = customer.CustomerID;
            int customerTypeId = customer.CustomerTypeID; 

            Customer rollUpCustomer = this.GetCustomerByID(rollUpCustomerId);
            SponsorCommission commission = rollUpCustomer.CustomerType.SponsorCommissions
                .SingleOrDefault(
                    x => x.SponsorCustomerTypeID == rollUpCustomer.CustomerTypeID
                        && x.CustomerTypeID == customerTypeId
                );

            DateTime now = DateTime.Now;

            decimal totalAmount = this.GetTotalAmountOfDay(rollUpCustomerId, DateTime.Now.Date);

            decimal maxProfitAmount = rollUpCustomer.CustomerType.MaxDailyProfit;
            decimal remain = maxProfitAmount - totalAmount;
            string desc = "";

            switch (accountType)
            {
                case AccountType.Direct:
                    desc = AppResource.MessageDirectCommission;
                    break;
                case AccountType.RollUp:
                    desc = "Hoa hồng rollup";
                    break;
                default:
                    break;
            }

            decimal commissionAmount = commission.Amount;
            // so tien khach hang nay lanh duoc sau khi tru cho tong so tien cua nhung khach hang ben duoi
            commissionAmount = commissionAmount - sumAmount;
            sumAmount = sumAmount + commissionAmount;

            if (remain > commissionAmount)
            {
                remain = commissionAmount;
            }
            else
            {
                desc = desc + String.Format("- Vượt max tiền trong ngày, đúng = {0}, thực tế = {1}",
                    commission.Amount, remain);
            }

            TransactionHistory history = new TransactionHistory();
            history.CustomerID = rollUpCustomerId;
            history.SponsoredCustomerID = customerId;
            history.SponsoredCustomer = customer;
            history.AccountType = accountType;
            history.AccountDate = now;
            history.Amount = remain * new decimal(0.8);
            history.ProductAmount = remain * new decimal(0.2);
            history.Description = desc;
            history.CreatedUserId = 99999;
            history.CreatedAt = now;
            history.UpdatedUserId = 99999;
            history.UpdatedAt = now;
            InsertTransactionHistory(history);

            if (sumAmount < maxSponsorAmount && rollUpCustomer.SponsorCustomerID != null)
            {
                CreateTransaction(customer,
                    (int)rollUpCustomer.SponsorCustomerID, AccountType.RollUp, 
                    sumAmount, maxSponsorAmount);
            }
        }

        public void DeleteCustomer(int customerID)
        {
            Customer customer = context.Customers.Find(customerID);
            context.Customers.Remove(customer);
        }

        public void DeleteTransactionByCustomerId(int customerId)
        {
            IEnumerable<TransactionHistory> list = context.TransactionHistories
                .Where(x => x.CustomerID == customerId
                    || x.SponsoredCustomerID == customerId);
            context.TransactionHistories.RemoveRange(list);
        }

        public CustomerType GetCustomerTypeById(int customerTypeId)
        {
            CustomerType customerType = context.CustomerTypes
                    .Where(s => s.ID == customerTypeId).FirstOrDefault<CustomerType>();

            return customerType;
        }

        public SponsorCommission GetSponsorCommission(int fromCustomerTypeId, int toCustomerTypeId)
        {
            SponsorCommission commission = context.SponsorCommissions
                .Where(x => x.SponsorCustomerTypeID == fromCustomerTypeId
                    && x.CustomerTypeID == toCustomerTypeId).SingleOrDefault<SponsorCommission>();
            return commission;
        }

        public List<SponsorCommission> GetSponsorCommission(int fromCustomerTypeId)
        {
            List<SponsorCommission> commissions = context.SponsorCommissions
                .Where(x => x.SponsorCustomerTypeID == fromCustomerTypeId
                    && x.UpgradeDays > 0)
                .OrderBy(x => x.UpgradeDays)
                .ToList<SponsorCommission>();
            return commissions;
        }

        public IEnumerable<Product> GetProducts()
        {
            return context.Products.Include(x => x.Unit);
        }

        public UniLevel GetUniLevel(int level)
        {
            UniLevel uniLevel = context.UniLevels
                .Where(x => x.Level == level)
                .SingleOrDefault<UniLevel>();

            return uniLevel;
        } 

        public void UpdateCustomer(Customer customer)
        {
            customer.Name = CultureInfo.CurrentCulture.TextInfo.ToTitleCase(customer.Name);
            context.Entry(customer).State = EntityState.Modified;
        }

        public decimal GetTotalHistoryPoint(int customerID)
        {
            /*
            var query = (
                from history in context.PointHistories
                where history.CustomerID == 1
                group history by history.CustomerID into historyGroup
                select new { 
                    TotalPoint = historyGroup.Key 
                });
            */

            var query = "SELECT Sum(Point) AS TotalPoint"
                + " FROM TransactionHistory"
                + " WHERE CustomerID = @CustomerID"
                + " And (AccountType = " + (int)AccountType.Pairing
                + " Or AccountType = " + (int)AccountType.MinusPoint + ")"
                + " GROUP BY CustomerID";
            SqlParameter param = new SqlParameter("CustomerID", customerID);

            decimal point = context.Database.SqlQuery<decimal>(query, param).FirstOrDefault();

            return point;
        }

        public decimal GetTotalLeftPoint(int customerID)
        {
            var query = "SELECT Sum(LeftPoint) AS TotalPoint"
                + " FROM TransactionHistory"
                + " WHERE CustomerID = @CustomerID"
                + " And AccountType = " + (int)AccountType.MinusPoint
                + " GROUP BY CustomerID";
            SqlParameter param = new SqlParameter("CustomerID", customerID);

            decimal point = context.Database.SqlQuery<decimal>(query, param).FirstOrDefault();

            return point;
        }

        public decimal GetTotalRightPoint(int customerID)
        {
            var query = "SELECT Sum(RightPoint) AS TotalPoint"
                + " FROM TransactionHistory"
                + " WHERE CustomerID = @CustomerID"
                + " And AccountType = " + (int)AccountType.MinusPoint
                + " GROUP BY CustomerID";
            SqlParameter param = new SqlParameter("CustomerID", customerID);

            decimal point = context.Database.SqlQuery<decimal>(query, param).FirstOrDefault();

            return point;
        }

        public decimal GetTotalAmountOfDay(int customerID, DateTime? date)
        {
            if (date == null)
            {
                date = DateTime.Now.Date;
            }

            // TODO IMPORT - them dieu kien vo nua moi dung
            var query = "SELECT Sum(Amount) + Sum(ProductAmount) AS TotalAmount"
                + " FROM TransactionHistory"
                + " WHERE CustomerID = @CustomerID"
                + " AND AccountDate = @AccountDate"
                + " AND AccountType Not In (" 
                + ((int)AccountType.Active).ToString() + ", "
                + ((int)AccountType.Withdraw).ToString() + ")"
                + " GROUP BY CustomerID, AccountDate";
            SqlParameter param1 = new SqlParameter("CustomerID", customerID);
            SqlParameter param2 = new SqlParameter("AccountDate", date);

            decimal amount = context.Database.SqlQuery<decimal>(
                    query, param1, param2).FirstOrDefault();

            return amount;
        }

        /**
         * Lấy tổng số tiền 80
         */
        public decimal GetTotalAmount80(int customerID)
        {
            var query = "SELECT Sum(Amount) AS TotalAmount"
                + " FROM TransactionHistory"
                + " WHERE CustomerID = @CustomerID"
                + " AND " + GetAllCondition()
                + " GROUP BY CustomerID";
            SqlParameter param1 = new SqlParameter("CustomerID", customerID);

            decimal amount = context.Database.SqlQuery<decimal>(
                    query, param1).FirstOrDefault();

            return amount;
        }

        public decimal GetTotalAmount(string userName)
        {
            var query = "SELECT Sum(Amount) AS TotalAmount"
                + " FROM TransactionHistory"
                + " WHERE UserName = @UserName"
                + " AND " + GetAllCondition()
                + " GROUP BY CustomerID";
            SqlParameter param1 = new SqlParameter("UserName", userName);

            decimal amount = context.Database.SqlQuery<decimal>(
                    query, param1).FirstOrDefault();

            return amount;
        }

        public decimal GetTotalAmount20(int customerId)
        {
            var query = "SELECT Sum(ProductAmount) AS Amount"
                + " FROM TransactionHistory"
                + " WHERE CustomerID = @CustomerID"
                + " AND " + GetAllCondition()
                + " GROUP BY CustomerID";
            SqlParameter param1 = new SqlParameter("CustomerID", customerId);

            decimal amount = context.Database.SqlQuery<decimal>(query, param1).FirstOrDefault();

            return amount;
        }

        public List<CustomerWithAmount> GetCustomersWithAmount(int customerId = 0)
        {
            var query = "SELECT t1.CustomerId, t1.UserName, t1.Name, "
                + " t2.Amount80 As Amount80, t2.Amount20 As Amount20"
                + " FROM Customer t1"
                + " INNER JOIN (SELECT CustomerId, Sum(Amount) Amount80, Sum(ProductAmount) Amount20 "
                + "             FROM TransactionHistory "
                + "             WHERE " + GetAllCondition()
                + "             GROUP BY CustomerId) t2"
                + "     ON t1.CustomerId = t2.CustomerId";

            if (customerId != 0)
            {
                query += " WHERE t1.CustomerId = " + customerId.ToString();
            }

            List<CustomerWithAmount> customers = context.Database.SqlQuery<CustomerWithAmount>(query).ToList();

            return customers;
        }

        private string GetCondition()
        {
            return
                  " AccountType != " + (int)AccountType.MinusPoint
                + " And DeletedAt Is Null ";
        }

        private string GetAllCondition()
        {
            return
                  " AccountType != " + (int)AccountType.MinusPoint
                + " And TransactionStatus != " + (int)TransactionStatus.Cancel
                + " And DeletedAt Is Null ";
        }

        public string GetMaxUserName()
        {
            var query = "SELECT UserName"
                + " FROM Customer"
                + " WHERE UserName Like @UserName"
                + " ORDER BY UserName desc";
            SqlParameter param1 = new SqlParameter("UserName", "HH38%");
            string userName = context.Database.SqlQuery<string>(query, param1).FirstOrDefault();

            try
            {
                if (!string.IsNullOrEmpty(userName))
                {

                    userName = userName.Substring(4);
                    userName = "HH38" + (int.Parse(userName) + 1).ToString().PadLeft(6, '0');
                    return userName;
                }
            }
            catch (Exception)
            {
                
            }

            return "HH38000001";
        }

        public void Save()
        {
            context.SaveChanges();
        }

        private bool disposed = false;

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                if (disposing)
                {
                    context.Dispose();
                }
            }
            this.disposed = true;
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }
    }
}